/*
	EXEC SP_CREATE_NEW_WIP_FLUSH_MASTER 273
	select * FROM wip_master
This Processure should be supplied with 0 (zero) as an argument, if it has to be run for all the lots in the System.
If for a particualr lot, Wip Information has to be updated means, it has to be supplied lotID as argument.
*/

ALTER PROC SP_CREATE_NEW_WIP_FLUSH_MASTER @LOTID INT AS
BEGIN
	SET NOCOUNT ON
 	DECLARE @wip_cursor CURSOR
	DECLARE @lot_Id INT
	DECLARE @processStep_Id INT
	DECLARE @location_Id INT
	DECLARE @IS_INTRANSIT INT
 	DECLARE @qty_In INT
	DECLARE @lot_Number VARCHAR(50)
	DECLARE @ps_Type_Name VARCHAR(50) 
	DECLARE @date_in DATETIME
	DECLARE @WIP_TABLE TABLE ( LOT_ID INT, PROCESSSTEP_ID INT, LOCATION_ID INT, LOT_NUMBER VARCHAR(50), PS_TYPE_NAME VARCHAR(50), IS_INTRANSIT INT, QTY_IN INT, DATE_IN DATETIME )

	DELETE FROM WIP_MASTER

	INSERT INTO @WIP_TABLE
	SELECT LT.LOT_ID, LT.PROCESSSTEP_ID, LT.LOCATION_ID, LT.LOT_NUMBER, PS.PS_TYPE_NAME, 1 AS IS_INTRANSIT, 
			(CASE WHEN PS.PS_TYPE_NAME = 'FAB' OR PS.PS_TYPE_NAME = 'WAFER STORE' OR PS.PS_TYPE_NAME = 'SORT' THEN LT.WAFER_QTY_IN ELSE LT.QTY_IN END ) AS QTY_IN, LT.DATE_IN FROM LOT_TABLE LT, PROCESS_STEP PS WHERE PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID AND ( LT.LOT_CURRENT_STATUS = 'Running' OR LT.LOT_CURRENT_STATUS = 'Hold' ) AND (LT.WAFER_QTY_IN > 0 OR LT.QTY_IN > 0)
	UNION --TRANSIT LOTS
	 SELECT LT.LOT_ID, PS.PROCESSSTEP_ID, LT.LOCATION_ID, LT.LOT_NUMBER, PS.PS_TYPE_NAME, 0 AS IS_INTRANSIT, SL.SHIP_QTY AS QTY_IN, SL.SHIP_DATE AS DATE_IN
		FROM SHIPPED_LOT SL, LOT_TABLE LT, PRODUCT P, PROCESS_STEP PS
		WHERE LT.LOT_ID = SL.LOT_ID 		
			AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
			AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
			AND SL.STATUS = 'TRANSIT'
	
	SELECT TOP 1 @LOT_ID = LOT_ID, @PROCESSSTEP_ID = PROCESSSTEP_ID, @LOCATION_ID = LOCATION_ID, @LOT_NUMBER = LOT_NUMBER, @PS_TYPE_NAME = PS_TYPE_NAME, @IS_INTRANSIT = IS_INTRANSIT, @QTY_IN = QTY_IN, @DATE_IN = DATE_IN FROM @WIP_TABLE
 	WHILE @LOT_ID != 0
 	BEGIN
		EXEC SP_EXPECTED_DATE_QTY_FINISHED_GOODS @PROCESSSTEP_ID, @PS_TYPE_NAME, @LOT_ID, @LOT_NUMBER, @QTY_IN, @DATE_IN, @LOCATION_ID, @IS_INTRANSIT
		DELETE FROM @WIP_TABLE WHERE LOT_ID = @LOT_ID
		SET @LOT_ID = 0
		SET @PROCESSSTEP_ID = NULL
		SET @LOCATION_ID = NULL
		SET @LOT_NUMBER = NULL
		SET @PS_TYPE_NAME = NULL
		SET @IS_INTRANSIT = 1
		SET @QTY_IN = 0
		SET @DATE_IN = NULL
		SELECT TOP 1 @LOT_ID = LOT_ID, @PROCESSSTEP_ID = PROCESSSTEP_ID, @LOCATION_ID = LOCATION_ID, @LOT_NUMBER = LOT_NUMBER, @PS_TYPE_NAME = PS_TYPE_NAME, @IS_INTRANSIT = IS_INTRANSIT, @QTY_IN = QTY_IN, @DATE_IN = DATE_IN FROM @WIP_TABLE
	END
END

